﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SuperMarketSystem
{
    public partial class OutForm : Form
    {
        public OutForm()
        {
            InitializeComponent();
            fillOut();//填充销售记录表
        }

        private void btnAddOut_Click(object sender, EventArgs e)
        {
            TSM tsm = new TSM();//新建一个记录
            Form tableForm = new TSMGet(ref tsm);//新建一个记录接收窗口接收记录
            tableForm.ShowDialog();//打开窗口
            if (tsm.ID != -1)//判断是否接收到记录
            {
                bool isSelect = false;
                int isUpdate = 1;
                String sql1 = String.Format("insert into outlist(outID,itemID,outprice,outnum,outsum,outroot,outdate)" +
                    " values({0},{1},{2},{3},{4},{5},'{6}')",tsm.ID,tsm.itemID,tsm.price,tsm.num,tsm.sum,tsm.root, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                try
                {
                    int remain = -1;
                    int remin = -2;
                    SqlConnection connection = MySqlServerUtils.getMySqlServerConnection();
                    SqlConnection connection2 = MySqlServerUtils.getMySqlServerConnection();
                    SqlConnection connection3 = MySqlServerUtils.getMySqlServerConnection();
                    SqlCommand command1 = new SqlCommand(sql1, connection);
                    String sql2 = String.Format("select itemstore,itemmin from stores where itemID={0}", tsm.itemID);
                    String sql3 = String.Format("update stores set itemstore=itemstore-{0} where itemID={1}", tsm.num, tsm.itemID);
                    SqlCommand command2 = new SqlCommand(sql2, connection2);
                    SqlCommand command3 = new SqlCommand(sql3, connection3);
                    SqlDataReader mySqlDataReader = command2.ExecuteReader();
                    isSelect = mySqlDataReader.Read();
                    if (isSelect)
                    {
                        remain = Convert.ToInt32(mySqlDataReader["itemstore"]);
                        remin = Convert.ToInt32(mySqlDataReader["itemmin"]);
                    }
                    mySqlDataReader.Close();
                    if (remain > 0 && remin > 0 && remain - tsm.num>= remin)
                    {
                        isUpdate = command3.ExecuteNonQuery();
                    }
                    if (isSelect != false && isUpdate != 0)
                    {
                        command1.ExecuteNonQuery();
                    }
                    else
                    {
                        MessageBox.Show("操作回滚", "对应书目库存不够或者不存在书目，无法更新");
                    }
                    MySqlServerUtils.closeSqlServerConnection(connection);
                    MySqlServerUtils.closeSqlServerConnection(connection2);
                    MySqlServerUtils.closeSqlServerConnection(connection3);
                }
                catch (Exception e2)
                {

                }
                fillOut();
            }
        }
        private void fillOut()//填充销售记录表
        {
                Out.Items.Clear();
                String[] tbStrings = { "outID", "itemID", "outprice", "outnum", "outsum", "outroot", "outdate" };
                SqlConnection connectionLogin = MySqlServerUtils.getMySqlServerConnection();
                String smCommand = "select * from outlist";
                SqlCommand commandLogin = new SqlCommand(smCommand, connectionLogin);
                SqlDataReader smdr = commandLogin.ExecuteReader();
                while (smdr.Read())
                {
                    ListViewItem listToAdd = new ListViewItem();
                    listToAdd.Text = smdr[tbStrings[0]].ToString();
                    for (int i = 1; i < Out.Columns.Count; i++)
                    {
                        listToAdd.SubItems.Add(smdr[tbStrings[i]].ToString());
                    }
                    Out.Items.Add(listToAdd);
                }
                try
                {
                    connectionLogin.Close();
                }
                catch (Exception exception)
                {
                    MessageBox.Show(exception.StackTrace);
                }
        }

        private void btnReset_Click(object sender, EventArgs e)//重置按钮点击事件
        {
            fillOut();
        }

        private void btnSearchOut_Click(object sender, EventArgs e)//查询销售记录点击事件
        {
            SearchLimit searchLimit = new SearchLimit("ID");
            searchLimit.ShowDialog();
            int limitID=-1;
            if (MyOtherUtils.checkInt(searchLimit.returnByName()))
            {
                limitID = searchLimit.returnByID();
            }
            if (limitID != -1)
            {
                Out.Items.Clear();
                String[] tbStrings = { "outID", "itemID", "outprice", "outnum", "outsum", "outroot", "outdate" };
                String sql = String.Format("select * from outlist where itemID={0}", limitID);
                SqlConnection connectionLogin = MySqlServerUtils.getMySqlServerConnection();
                SqlCommand commandLogin = new SqlCommand(sql, connectionLogin);
                SqlDataReader smdr = commandLogin.ExecuteReader();
                int numSum = 0;
                double sumSum = 0;
                while (smdr.Read())
                {
                    ListViewItem listToAdd = new ListViewItem();
                    listToAdd.Text = smdr[tbStrings[0]].ToString();
                    for (int i = 1; i < Out.Columns.Count; i++)
                    {
                        listToAdd.SubItems.Add(smdr[tbStrings[i]].ToString());
                    }
                    numSum += Convert.ToInt32(smdr["outnum"]);
                    sumSum += Convert.ToDouble(smdr["outsum"]);
                    Out.Items.Add(listToAdd);
                }
                try
                {
                    connectionLogin.Close();
                }
                catch (Exception exception)
                {
                    MessageBox.Show(exception.StackTrace);
                }
            }
        }

        private void btnCalOutByMonth_Click(object sender, EventArgs e)//按照月统计查询
        {
            String dateString = DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss");
            TimePick timePick = new TimePick(ref dateString);
            timePick.ShowDialog();
            dateString = timePick.returnTime().ToString("yyyy-MM-dd HH:mm:ss");
            String sql = String.Format("SELECT * FROM (SELECT * FROM outlist WHERE MONTH(outdate) = MONTH('{0}') AND YEAR(outdate)=YEAR('{1}'))a", dateString, dateString);
            Out.Items.Clear();
            String[] tbStrings = { "outID", "itemID", "outprice", "outnum", "outsum", "outroot", "outdate" };
            SqlConnection connectionLogin = MySqlServerUtils.getMySqlServerConnection();
            SqlCommand commandLogin = new SqlCommand(sql, connectionLogin);
            SqlDataReader smdr = commandLogin.ExecuteReader();
            int numSum = 0;
            double sumSum = 0;
            while (smdr.Read())
            {
                ListViewItem listToAdd = new ListViewItem();
                listToAdd.Text = smdr[tbStrings[0]].ToString();
                for (int i = 1; i < Out.Columns.Count; i++)
                {
                    listToAdd.SubItems.Add(smdr[tbStrings[i]].ToString());
                }
                numSum += Convert.ToInt32(smdr["outnum"]);
                sumSum += Convert.ToDouble(smdr["outsum"]);
                Out.Items.Add(listToAdd);
            }
            smdr.Close();
            String sql2 = String.Format("SELECT SUM(outnum) as out_sum,SUM(outsum) as sum_sum FROM (SELECT * FROM outlist WHERE MONTH(outdate) = MONTH('{0}') AND YEAR(outdate)=YEAR('{1}'))a", dateString, dateString);
            SqlCommand commandLogin2 = new SqlCommand(sql2, connectionLogin);
            SqlDataReader smdr2 = commandLogin2.ExecuteReader();
            if (smdr2.Read())
            {
                if (smdr2["out_sum"] != DBNull.Value)
                    numSum = Convert.ToInt32(smdr2["out_sum"]);
                if (smdr2["sum_sum"] != DBNull.Value)
                    sumSum = Convert.ToDouble(smdr2["sum_sum"]);
            }
            String lastAdd = timePick.returnTime().ToString("yyyy年--MM月");
            ListViewItem listToAddNeo = new ListViewItem();
            listToAddNeo.Text = "统计时间：";
            listToAddNeo.SubItems.Add(lastAdd);
            listToAddNeo.SubItems.Add("当月总销售量：");
            listToAddNeo.SubItems.Add(numSum.ToString());
            listToAddNeo.SubItems.Add("当月总交易额：");
            listToAddNeo.SubItems.Add(sumSum.ToString());
            Out.Items.Add(listToAddNeo);
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
        }

        private void btnCalOutByDay_Click(object sender, EventArgs e)//按照天统计查询
        {
            String dateString = DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss");
            TimePick timePick = new TimePick(ref dateString);
            timePick.ShowDialog();
            dateString = timePick.returnTime().ToString("yyyy-MM-dd HH:mm:ss");
            String sql = String.Format("SELECT * FROM (SELECT * FROM outlist WHERE MONTH(outdate) = MONTH('{0}') AND YEAR(outdate)=YEAR('{1}') AND DAY(outdate)=DAY('{2}'))a", dateString, dateString,dateString);
            Out.Items.Clear();
            String[] tbStrings = { "outID", "itemID", "outprice", "outnum", "outsum", "outroot", "outdate" };
            SqlConnection connectionLogin = MySqlServerUtils.getMySqlServerConnection();
            SqlCommand commandLogin = new SqlCommand(sql, connectionLogin);
            SqlDataReader smdr = commandLogin.ExecuteReader();
            int numSum = 0;
            double sumSum = 0;
            while (smdr.Read())
            {
                ListViewItem listToAdd = new ListViewItem();
                listToAdd.Text = smdr[tbStrings[0]].ToString();
                for (int i = 1; i < Out.Columns.Count; i++)
                {
                    listToAdd.SubItems.Add(smdr[tbStrings[i]].ToString());
                }
                numSum += Convert.ToInt32(smdr["outnum"]);
                sumSum += Convert.ToDouble(smdr["outsum"]);
                Out.Items.Add(listToAdd);
            }
            smdr.Close();
            String sql2 = String.Format("SELECT SUM(outnum) as out_sum,SUM(outsum) as sum_sum FROM (SELECT * FROM outlist WHERE MONTH(outdate) = MONTH('{0}') AND YEAR(outdate)=YEAR('{1}') AND DAY(outdate)=DAY('{2}'))a", dateString, dateString, dateString);
            SqlCommand commandLogin2 = new SqlCommand(sql2, connectionLogin);
            SqlDataReader smdr2 = commandLogin2.ExecuteReader();
            if (smdr2.Read())
            {
                if (smdr2["out_sum"] != DBNull.Value)
                    numSum = Convert.ToInt32(smdr2["out_sum"]);
                if (smdr2["sum_sum"] != DBNull.Value)
                    sumSum = Convert.ToDouble(smdr2["sum_sum"]);
            }
            String lastAdd = timePick.returnTime().ToString("yyyy年--MM月--dd日");
            ListViewItem listToAddNeo = new ListViewItem();
            listToAddNeo.Text = "统计时间：";
            listToAddNeo.SubItems.Add(lastAdd);
            listToAddNeo.SubItems.Add("当日总销售量：");
            listToAddNeo.SubItems.Add(numSum.ToString());
            listToAddNeo.SubItems.Add("当日总交易额：");
            listToAddNeo.SubItems.Add(sumSum.ToString());
            Out.Items.Add(listToAddNeo);
            try
            {
                connectionLogin.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.StackTrace);
            }
        }
    }
}
